using System;
using System.Collections.Generic;
using System.Text;
using MacomberMap.Common.Database_Interface.CIM;
using System.Windows.Forms;
using MacomberMap.Common.Internals;
using System.Xml;
using MacomberMap.Common.Database_Interface.CIM.CIM_Interface;
using MacomberMap.Common.Database_Interface.CIM.Components;
using System.Threading;
using MacomberMap.Common.Database_Interface;
using MacomberMap.Common.Logging;
using MacomberMap.Common.Events;
using MacomberMap.Common.Components;
using System.Diagnostics;
using System.IO;
using MacomberMap.Common.Database_Interface.CIM.One_Line_Interface;
using MacomberMap.Common.User_Interfaces.One_Lines;
using MacomberMap.Common.User_Interfaces.One_Lines.Elements;
using System.Data.SQLite;
using System.Data.OleDb;
using System.Data;
using System.Data.Common;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using MacomberMap.Common.Types;
using System.Data.Odbc;

namespace MacomberMap.Loader.Export
{
    /// <summary>
    /// This class handles exporting of the trace for Outage Scheduler.
    /// </summary>
    public class MM_Exporter_Contingencies : MM_Exporter
    {
        #region Variable Declarations
        /// <summary>Create an encoding without BOM</summary>
        private static Encoding UTF8NoBOM = new UTF8Encoding(false);


        /// <summary>Return the current progress</summary>
        public override float Progress
        {
            get { return Current / Total; }
        }

        /// <summary>The current step of export</summary>
        private float Current = 0;

        /// <summary>The total number of steps for export</summary>
        private float Total = 0;

        /// <summary>The collection of seed elements that should be parsed</summary>
        private Dictionary<Object, bool> ElementsToParse = new Dictionary<Object, bool>();

        /// <summary>Our matching of RASs and associated elements</summary>
        private Dictionary<CIM_Element, CIM_Element[]> RAS = new Dictionary<CIM_Element, CIM_Element[]>();

        /// <summary>Our collection of seed elements</summary>
        private List<String> SeedElements = new List<string>();

        /// <summary>Elements to be ignored on export when parameters match</summary>
        private Dictionary<String, String> IgnoreOnTrace = new Dictionary<string, string>();

        /// <summary>The elements that are excluded</summary>
        private Dictionary<MM_rdfID, bool> ExcludedElements = new Dictionary<MM_rdfID, bool>();

        /// <summary>The attribute indicating whether something is a contingency element</summary>
        private string CtgAttribute;

        /// <summary>Whether seed elements in double-circuit contingencies are included regardless of CtgAttribute</summary>
        private bool IncludeSeedsInDoubles = true;

        /// <summary>Our collection of contingency groups</summary>
        private Dictionary<String, MM_Contingency_Grouping> CtgGroups = new Dictionary<String, MM_Contingency_Grouping>();

        /// <summary>The maximum number of exportable items</summary>
        public int MaxExport = -1;

        /// <summary>The target path for output</summary>
        public String TargetDirectory;

        /// <summary>The oracle database</summary>
        private MM_Database_Oracle oDb;

        /// <summary>The CIM database</summary>
        private MM_Database_CIM cDb;

        /// <summary>Boolean if the Contingencies.SQLite file exist</summary>
        public bool CTGFileMissing;

        #endregion

        #region mRID updating
        /// <summary>
        /// Retrieve the last MRID
        /// </summary>
        /// <returns></returns>
        public static int GetMRID()
        {
            return ++MM_Database_CIM.MaxMRID;
        }

        #endregion

        #region Initialization
        /// <summary>
        /// Initialize the OS Exporter
        /// </summary>        
        /// <param name="TargetDirectory">The target directory</param>
        /// <param name="Parent">The list view in which this item will reside</param>
        /// <param name="oDb">The Oracle database</param>
        /// <param name="cDb">The CIM database</param>
        public MM_Exporter_Contingencies(String TargetDirectory, ListView Parent, MM_Database_Oracle oDb, MM_Database_CIM cDb)
            : base(Parent, "Contingency Exporter")
        {
            this.oDb = oDb;
            this.cDb = cDb;
            this.TargetDirectory = Path.GetDirectoryName(MM_Database_CIM.CIMFileName) + "\\";

            //Build our list of seed elements
            XmlElement CtgDef = MM_Configuration.xConfig.SelectSingleNode("/MacomberMap_Loader/ContingencyDefinitions") as XmlElement;
            if (CtgDef.HasAttribute("Attribute"))
                this.CtgAttribute = CtgDef.Attributes["Attribute"].Value;
            if (CtgDef.HasAttribute("MaxExport"))
                this.MaxExport = XmlConvert.ToInt32(CtgDef.Attributes["MaxExport"].Value);
            if (CtgDef.HasAttribute("IncludeSeedsInDoubles"))
                this.IncludeSeedsInDoubles = XmlConvert.ToBoolean(CtgDef.Attributes["IncludeSeedsInDoubles"].Value);
                
            //Build a list of substations 
            Dictionary<String, CIM_Element> Substations = new Dictionary<string, CIM_Element>(StringComparer.CurrentCultureIgnoreCase);
            foreach (CIM_Element Sub in MM_Database_CIM.ElementsOfType("cim:Substation"))
                Substations.Add(Sub.Name, Sub);

            //Retrieve our seeds, groups, and tracing information
            foreach (XmlNode xElem in CtgDef.ChildNodes)
                if (xElem.Name == "SeedElement")
                    SeedElements.Add(xElem.Attributes["value"].Value);
                else if (xElem.Name == "IgnoreOnTrace" && xElem.Attributes["Where"] == null)
                    IgnoreOnTrace.Add(xElem.Attributes["value"].Value,null);
                else if (xElem.Name == "IgnoreOnTrace")
                    IgnoreOnTrace.Add(xElem.Attributes["value"].Value, xElem.Attributes["Where"].Value);
                else if (xElem.Name == "ExcludedElement")
                    ExcludedElements.Add(new MM_rdfID(xElem.Attributes["value"].Value), true);
                else if (xElem.Name == "Grouping")
                    CtgGroups.Add(xElem.Attributes["Name"].Value, new MM_Contingency_Grouping(xElem as XmlElement, CtgGroups, Substations));

        }


        /// <summary>
        /// Add a parameter to a query
        /// </summary>
        /// <param name="sCmd"></param>
        /// <param name="ParamName"></param>
        /// <param name="ParamValue"></param>
        private void AddParameter(DbCommand sCmd, String ParamName, Object ParamValue)
        {
            DbParameter dParam = sCmd.CreateParameter();
            dParam.ParameterName = ParamName;
            dParam.Value = ParamValue;
            sCmd.Parameters.Add(dParam);
        }

        /// <summary>        
        /// Add a record to the database
        /// </summary>
        /// <param name="sConn"></param>
        /// <param name="CommandText"></param>
        /// <param name="inValues"></param>
        /// <param name="sTrans"></param>
        public Int64 ExecuteCommand(DbConnection sConn, DbTransaction sTrans, String CommandText, params object[] inValues)
        {
            using (DbCommand sCmd = sConn.CreateCommand())
            {
                sCmd.CommandText = CommandText + "; SELECT last_insert_rowid() AS [ID]";
                sCmd.Transaction = sTrans;
                for (int a = 0; a < inValues.Length; a++)
                    if (inValues[a] is List<byte>)
                        AddParameter(sCmd, ":" + a.ToString(), (inValues[a] as List<byte>).ToArray());
                    else if (inValues[a] is List<UInt16>)
                    {
                        List<UInt16> OutShort = inValues[a] as List<UInt16>;
                        Byte[] outBytes = new byte[OutShort.Count * 2];
                        for (int b = 0; b < OutShort.Count; b++)
                            BitConverter.GetBytes(OutShort[b]).CopyTo(outBytes, b * 2);
                        AddParameter(sCmd, ":" + a.ToString(), outBytes);
                    }
                    else if (inValues[a] is MM_rdfID)
                    {
                        byte[] inBytes = (inValues[a] as MM_rdfID).Bytes;
                        AddParameter(sCmd, ":" + a.ToString(), inBytes);
                    }
                    else if (inValues[a] is IEnumerable<CIM_Element>)
                    {
                        using (MemoryStream OutStream = new MemoryStream())
                        using (BinaryWriter bW = new BinaryWriter(OutStream))
                        {
                            foreach (CIM_Element Elem in (inValues[a] as IEnumerable<CIM_Element>))
                            {
                                bW.Write(Elem.TypeByte);
                                bW.Write(Elem.Index);
                            }
                            bW.Flush();
                            AddParameter(sCmd, ":" + a.ToString(), OutStream.ToArray());
                        }
                    }

                    else if (inValues[a] is MM_rdfID[])
                    {
                        List<Byte> outBytes = new List<byte>();
                        foreach (MM_rdfID rdfID in inValues[a] as MM_rdfID[])
                            outBytes.AddRange(rdfID.Bytes);
                        AddParameter(sCmd, ":" + a.ToString(), outBytes.ToArray());
                    }
                    else if (inValues[a] is string)
                        AddParameter(sCmd, ":" + a.ToString(), MM_Repository.TitleCase((string)inValues[a]));
                    else
                        AddParameter(sCmd, ":" + a.ToString(), inValues[a]);
                Current++;
                return Convert.ToInt64(sCmd.ExecuteScalar());
            }

        }

        /// <summary>
        /// Build our list of elements to be marked as seed elements
        /// <remarks>Updated 12/8/11 - Added in M* contingencies, which will be manually passed-through.</remarks>
        /// </summary>
        /// <param name="CtgGroups"></param>
        /// <param name="OutgoingDefinitions"></param>
        private void LocateSeedElements(Dictionary<string, MM_Contingency_Grouping> CtgGroups, Dictionary<string, MM_Contingency_Definition> OutgoingDefinitions)
        {           
            
            //Match our seeds to the new version                
            foreach (CIM_Element DCtg in MM_Database_CIM.ElementsOfType("cim:Contingency"))
                if (DCtg.Name.StartsWith("D"))
                {
                    ElementsToParse.Add(DCtg.rdfID, false);
                    this.Total++;
                }
                else if (DCtg.Name.StartsWith("M"))
                {
                    MM_Contingency_Definition NewDef = new MM_Contingency_Definition(DCtg.rdfID, DCtg.Name, DCtg.LongName, true);
                    foreach (CIM_Element Elem in DCtg.Elements)
                    {
                        NewDef.Seeds.Add(Elem);
                        NewDef.FullElements.Add(Elem);
                    }
                    NewDef.Seeds.TrimExcess();
                    NewDef.FullElements.TrimExcess();
                    foreach (MM_Contingency_Grouping cGrp in CtgGroups.Values)
                        if (cGrp.ContingencyInGroup(NewDef, NewDef.FullElements))
                            NewDef.Groups.Add(cGrp);
                    OutgoingDefinitions.Add(NewDef.Name, NewDef);
                }
                else
                {
                    CIM_Element RAS = DCtg.Parse("cim:RemedialActionScheme");
                    if (RAS != null)
                        this.RAS.Add(RAS, DCtg.Elements);
                }                                                                                                              
            
           

            //Now, go through all of our seed elements, and add that list.
            //TODO: As per Nitika's request - look at the CtgAttribute for both winding & XF.
            //foreach (String ElemType in SeedElements)
            //    foreach (CIM_Element Elem in MM_Database_CIM.ElementsOfType(ElemType))
            //        if (!ExcludedElements.ContainsKey(Elem.rdfID) && (String.IsNullOrEmpty(CtgAttribute) || String.IsNullOrEmpty(Elem[CtgAttribute]) || XmlConvert.ToBoolean(Elem[CtgAttribute])))
            //        {
            //            ElementsToParse.Add(Elem, true);
            //            this.Total++;
            //        }
            foreach (String ElemType in SeedElements)
                foreach (CIM_Element Elem in MM_Database_CIM.ElementsOfType(ElemType))
                {
                      if (!ExcludedElements.ContainsKey(Elem.rdfID) &&
                        (Elem.Type != "cim:TransformerWinding" && (String.IsNullOrEmpty(CtgAttribute) || String.IsNullOrEmpty(Elem[CtgAttribute]) || XmlConvert.ToBoolean(Elem[CtgAttribute]))) ||
                        (Elem.Type == "cim:TransformerWinding" && (String.IsNullOrEmpty(CtgAttribute) || String.IsNullOrEmpty(Elem.Parse("cim:PowerTransformer")[CtgAttribute]) || XmlConvert.ToBoolean(Elem.Parse("cim:PowerTransformer")[CtgAttribute]))))
                    {
                        ElementsToParse.Add(Elem, true);
                        this.Total++;
                    }
                }
        }

        /// <summary>
        /// Create contingency output tables
        /// </summary>
        /// <param name="sPos"></param>
        private void CreateContingencyFile(SQLiteConnection sPos)
        {
            ExecuteCommand(sPos, null, "CREATE TABLE Element_Positions ([INDEX] INTEGER PRIMARY KEY, [Contingency] INTEGER, [Element] INTEGER, [Left] FLOAT, [Top] FLOAT, [Width] FLOAT, [Height] FLOAT)");
            ExecuteCommand(sPos, null, "CREATE INDEX Position_Contingency ON Element_Positions ([Contingency])");
            ExecuteCommand(sPos, null, "CREATE INDEX Position_Element ON Element_Positions ([Element])");
            ExecuteCommand(sPos, null, "CREATE TABLE Contingencies ([INDEX] INTEGER PRIMARY KEY, [Name] VARCHAR, [Description] VARCHAR, [Radial] INTEGER, [Groups] VARCHAR, [Left] FLOAT, [Top] FLOAT, [Right] FLOAT, [Bottom] FLOAT, [Connections] VARCHAR)");
            ExecuteCommand(sPos, null, "CREATE TABLE Elements ([TEID] INTEGER PRIMARY KEY, [Name] VARCHAR, [Substation] VARCHAR, [Voltage] VARCHAR, [ElemType] VARCHAR, [PSSEId] VARCHAR, [FromBus] INTEGER, [ToBus] INTEGER, [Owner] INTEGER, [Operator] INTEGER)");
            ExecuteCommand(sPos, null, "CREATE TABLE Contingency_Elements ([Contingency] INTEGER, [TEID] INTEGER)");
            ExecuteCommand(sPos, null, "CREATE INDEX Contingency_Number ON Contingency_Elements ([Contingency])");
            ExecuteCommand(sPos, null, "CREATE INDEX Contingency_TEID ON Contingency_Elements ([TEID])");
            ExecuteCommand(sPos, null, "CREATE TABLE Companies ([TEID] INTEGER PRIMARY KEY, [Name] VARCHAR, [PrimaryPhone] VARCHAR, [DUNS] VARCHAR)");
            ExecuteCommand(sPos, null, "CREATE TABLE Reserved_Contingency_Names ([Name] VARCHAR PRIMARY KEY)");
            ExecuteCommand(sPos, null, "CREATE TABLE Contingency_Seeds ([Name] VARCHAR, [rdfID] VARCHAR, [TEID] INTEGER)");
            ExecuteCommand(sPos, null, "CREATE UNIQUE INDEX Contingency_Seeds_TEIDs ON Contingency_Seeds ([TEID])");
        }


        /// <summary>
        /// Retrieve our substation to county linkages
        /// </summary>
        /// <returns></returns>
        private DataTable RetrieveSubstationToCountyLinkages()
        {
            using (DataTable BusToCounty = new DataTable("BusToCounty"))
            using (DataTable Coordinates = new DataTable("Coordinates"))
            {
                using (OdbcConnection oConn = new OdbcConnection("Driver={Microsoft Access Text Driver (*.txt, *.csv)};Dbq=" + TargetDirectory + ";Extensions=asc,csv,tab,txt;"))                
                {
                    oConn.Open();
                    using (OdbcDataAdapter oAdap = new OdbcDataAdapter("Select * from [Substation to county mappings.csv]", oConn))
                        oAdap.Fill(BusToCounty);
                    using (OdbcDataAdapter oAdap = new OdbcDataAdapter("Select * from [Coordinates.csv]", oConn))
                        oAdap.Fill(Coordinates);
                    BusToCounty.PrimaryKey = new DataColumn[] { BusToCounty.Columns["Bus #"] };
                    Coordinates.PrimaryKey = new DataColumn[] { Coordinates.Columns["Station"] };
                }


                //Create our mappings
                Dictionary<CIM_Element, DataRow[]> SubMaps = new Dictionary<CIM_Element, DataRow[]>();
                int NodeNumber;
                foreach (CIM_Element Substation in MM_Database_CIM.ElementsOfType("cim:Substation"))
                    foreach (CIM_Element Node in Substation.Nodes)
                        if (int.TryParse(Node["etx:ConnectivityNode.PSSEBusNumber"], out NodeNumber))
                        {
                            SubMaps.Add(Substation, new DataRow[] { BusToCounty.Rows.Find(NodeNumber), Coordinates.Rows.Find(Substation.Name) });
                            break;
                        }

                //Build our table of outgoing definitions
                DataTable SubToCounty = new DataTable("SubToCounty");
                SubToCounty.PrimaryKey = new DataColumn[] { SubToCounty.Columns.Add("Substation", typeof(CIM_Element)) };
                SubToCounty.Columns.Add("Long Name", typeof(String));
                SubToCounty.Columns.Add("County", typeof(MM_Boundary));
                SubToCounty.Columns.Add("WeatherZone", typeof(String));
                SubToCounty.Columns.Add("Latitude", typeof(float));
                SubToCounty.Columns.Add("Longitude", typeof(float));


                //Export our results
                Dictionary<String, String> WxMaps = new Dictionary<string, string>();
                WxMaps.Add("WZ_NORTH", "North");
                WxMaps.Add("WZ_NORTH_CENTRAL", "N. Central");
                WxMaps.Add("WZ_SOUTH_CENTRAL", "S. Central");
                WxMaps.Add("WZ_SOUTHERN", "South");
                WxMaps.Add("WZ_EAST", "East");
                WxMaps.Add("WZ_WEST", "West");
                WxMaps.Add("WZ_COAST", "Coast");
                WxMaps.Add("WZ_FAR_WEST", "Far West");

                foreach (KeyValuePair<CIM_Element, DataRow[]> kvp in SubMaps)
                {
                    String LongName;
                    if (kvp.Value[1] != null)
                        LongName = (string)kvp.Value[1]["LongName"];
                    else if (String.IsNullOrEmpty(LongName = kvp.Key.Attributes["cim:IdentifiedObject.description"]))
                        LongName = kvp.Key.Name;

                    MM_Boundary County;
                    String WxZone;
                    bool PointEstimated = kvp.Value[0] == null;
                    if (!PointEstimated)
                    {
                        County = MM_Repository.Counties[(string)kvp.Value[0]["County of Bus"]];
                        WxZone = MM_Repository.TitleCase((string)kvp.Value[0]["WEATHER"]);
                    }
                    else
                    {
                        String OutZone = WxZone = kvp.Key.Parse("cim:SubGeographicalRegion").Name;
                        if (!WxMaps.TryGetValue(WxZone, out OutZone))
                            if (MM_InputBox.InputBox("Weather Zone", "Please enter the easy-to-read name for " + WxZone, ref OutZone) == DialogResult.OK)
                            {
                                WxMaps.Add(WxZone, OutZone);
                                WxZone = OutZone;
                            }
                            else
                                WxZone = OutZone;
                        else
                            WxZone = OutZone;


                        //Now, locate our county
                        County = null;
                        if (kvp.Value[1] != null)
                            foreach (MM_Boundary Bound in MM_Repository.Counties.Values)
                                if (Bound.HitTest(new System.Drawing.PointF(Convert.ToSingle(kvp.Value[1]["Longtidude"]), Convert.ToSingle(kvp.Value[1]["Latitude"]))))
                                {
                                    County = Bound;
                                    break;
                                }
                        if (County == null)
                            Console.WriteLine("Unable to locate county for " + kvp.Key.Name);
                    }

                    DataRow NewRow = SubToCounty.NewRow();
                    NewRow["Substation"] = kvp.Key;
                    NewRow["Long Name"] = MM_Repository.TitleCase(LongName);
                    NewRow["County"] = County;
                    NewRow["WeatherZone"] = WxZone;
                    if (kvp.Value[1] != null)
                    {
                        NewRow["Latitude"] = kvp.Value[1]["Latitude"];
                        NewRow["Longitude"] = kvp.Value[1]["Longtidude"];
                    }
                }

                //JLH
                //using (StreamWriter sBounds = new StreamWriter(TargetDirectory + "SubstationToBounds.csv"))
                //{
                //    sBounds.WriteLine("\"Substation\",\"Long Name\",\"TEID\",\"rdfID\",\"County\",\"WeatherZone\",\"Est. Lat.\",\"Est. Lng.\",\"County estimated\"");
                //    foreach (KeyValuePair<CIM_Element, DataRow[]> kvp in SubMaps)
                //    {
                //        String LongName;
                //        if (kvp.Value[1] != null)
                //            LongName = (string)kvp.Value[1]["LongName"];
                //        else if (String.IsNullOrEmpty(LongName = kvp.Key.Attributes["cim:IdentifiedObject.description"]))
                //            LongName = kvp.Key.Name;

                //        MM_Boundary County;
                //        String WxZone;
                //        bool PointEstimated = kvp.Value[0] == null;
                //        if (!PointEstimated)
                //        {
                //            County = MM_Repository.Counties[(string)kvp.Value[0]["County of Bus"]];
                //            WxZone = MM_Repository.TitleCase((string)kvp.Value[0]["WEATHER"]);
                //        }
                //        else
                //        {
                //            String OutZone = WxZone = kvp.Key.Parse("cim:SubGeographicalRegion").Name;
                //            if (!WxMaps.TryGetValue(WxZone, out OutZone))
                //                if (MM_InputBox.InputBox("Weather Zone", "Please enter the easy-to-read name for " + WxZone, ref OutZone) == DialogResult.OK)
                //                {
                //                    WxMaps.Add(WxZone, OutZone);
                //                    WxZone = OutZone;
                //                }
                //                else
                //                    WxZone = OutZone;
                //            else
                //                WxZone = OutZone;


                //            //Now, locate our county
                //            County = null;
                //            if (kvp.Value[1] != null)
                //                foreach (MM_Boundary Bound in MM_Repository.Counties.Values)
                //                    if (Bound.HitTest(new System.Drawing.PointF(Convert.ToSingle(kvp.Value[1]["Longtidude"]), Convert.ToSingle(kvp.Value[1]["Latitude"]))))
                //                    {
                //                        County = Bound;
                //                        break;
                //                    }
                //            if (County == null)
                //                Console.WriteLine("Unable to locate county for " + kvp.Key.Name);
                //        }
                //        sBounds.WriteLine("\"{0}\",\"{1}\",{2},\"{3}\",\"{4}\",\"{5}\",{6},{7},{8}", MM_Repository.TitleCase(kvp.Key.Name), MM_Repository.TitleCase(LongName), kvp.Key.TEID, kvp.Key.rdfID, County == null ? "?" : MM_Repository.TitleCase(County.Name), WxZone, (kvp.Value[1] == null ? "" : kvp.Value[1]["Latitude"]), (kvp.Value[1] == null ? "" : kvp.Value[1]["Longtidude"]), PointEstimated);

                //        DataRow NewRow = SubToCounty.NewRow();
                //        NewRow["Substation"] = kvp.Key;
                //        NewRow["Long Name"] = MM_Repository.TitleCase(LongName);
                //        NewRow["County"] = County;
                //        NewRow["WeatherZone"] = WxZone;
                //        if (kvp.Value[1] != null)
                //        {
                //            NewRow["Latitude"] = kvp.Value[1]["Latitude"];
                //            NewRow["Longitude"] = kvp.Value[1]["Longtidude"];
                //        }
                //    }
                //}
                return SubToCounty;
            }
        }

        /// <summary>
        /// Export the full list of elements, and if requested, generate a one-line for them
        /// </summary>
        /// <param name="NewDef"></param>
        /// <param name="SeedElement"></param>
        /// <param name="TraceList"></param>
        /// <param name="CreateOneLines"></param>
        /// <param name="sPos"></param>
        /// <param name="olEditor"></param>
        /// <param name="ContingencyNumber"></param>
        private void ExportFullElements(MM_Contingency_Definition NewDef, CIM_Element SeedElement, Dictionary<CIM_Element, CIM_TraceList> TraceList, bool CreateOneLines, SQLiteConnection sPos, frmOneLineEditor olEditor, int ContingencyNumber)
        {
            Dictionary<UInt64, MM_OneLine_Element> OneLineElems = new Dictionary<ulong, MM_OneLine_Element>();
            if (CreateOneLines)
            {
                olEditor.CreateContingencyOneLine(SeedElement, TraceList);
                MM_OneLine_Viewer olView = olEditor.Controls["splMain"].Controls[0].Controls[0] as MM_OneLine_Viewer;
                olView.ContingencyLongName = NewDef.Name + " - " + NewDef.Description;
                olView.ExportDate = DateTime.Now;
                if (olView.BaseElement == null)
                {
                    olView.BaseElement = new MM_Element();
                    olView.BaseElement.ElemType = MacomberMap.Common.User_Interfaces.Components.MM_Overall_Display.FindElementType(SeedElement.Type.Split(':')[1]);
                    olView.BaseElement.Name = MM_Repository.TitleCase(SeedElement.Name);
                }

                foreach (MM_OneLine_Element Elem in olView.DisplayElements.Values)
                    OneLineElems.Add(Elem.TEID, Elem);
                foreach (MM_OneLine_Node Node in olView.DisplayNodes.Values)
                    if (!OneLineElems.ContainsKey(Node.TEID))
                        OneLineElems.Add(Node.TEID, Node);

                olView.SaveImage(TargetDirectory + "\\" + NewDef.Name + ".jpg");
                //  olView.SaveImage(@"\\cpwp004b\Ercot Scratch Share\mlegatt\Contingencies\" + NewDef.Name + " - " + olView.BaseElement.ElemType.Name + " " + olView.BaseElement.Name + ".jpg");
                using (XmlTextWriter xW = new XmlTextWriter(TargetDirectory + "\\" + NewDef.Name + ".MM_OneLine", UTF8NoBOM))
                    olView.WriteOneLineXml(xW, true);
                Application.DoEvents();
                GC.Collect(0);
                Application.DoEvents();
            }



            //Write out our positional information   
            using (SQLiteTransaction sTrans = sPos.BeginTransaction())
            {
                foreach (MM_OneLine_Element oElem in OneLineElems.Values)
                {
                    CIM_Element Elem = MM_Database_CIM.TEIDs[oElem.TEID];
                    CIM_Element[] Nodes = Elem.Nodes;
                    if (Elem.Type == "cim:ConnectivityNode")
                        Nodes = new CIM_Element[] { Elem };
                    //Write out our database information
                    ExecuteCommand(sPos, sTrans, "INSERT INTO Element_Positions ([Contingency], [Element], [Left], [Top], [Width], [Height]) VALUES (:0, :1, :2, :3, :4, :5)", ContingencyNumber, oElem.TEID, oElem.Left, oElem.Top, oElem.Width, oElem.Height);

                    String Sub = "";
                    if (Elem.Substation != null)
                        Sub = oElem.BaseElement.Substation.Name;
                    else if (Elem.ACSegmentStations.Length > 0)
                        Sub = Elem.ACSegmentStations[0].Name + " to " + Elem.ACSegmentStations[1].Name;
                    Object FromBus = DBNull.Value;
                    Object ToBus = DBNull.Value;
                    Object Owner = DBNull.Value;
                    Object Operator = DBNull.Value;

                    if (Elem.Owner != null)
                        Owner = Elem.Owner.TEID;
                    if (Elem.Operator != null)
                        Operator = Elem.Operator.TEID;

                    if (Nodes.Length >= 1)
                        FromBus = Nodes[0]["etx:ConnectivityNode.PSSEBusNumber"];
                    if (Nodes.Length >= 2)
                        ToBus = Nodes[1]["etx:ConnectivityNode.PSSEBusNumber"];

                    Object Voltage = DBNull.Value;
                    if (Elem.VoltageLevel != null)
                        Voltage = Elem.VoltageLevel.Name;

                    try
                    {
                        ExecuteCommand(sPos, sTrans, "REPLACE INTO Elements ([TEID], [Name], [Substation], [Voltage], [ElemType], [PSSEId], [FromBus], [ToBus], [Owner], [Operator]) VALUES (:0, :1, :2, :3, :4, :5, :6, :7, :8, :9)", oElem.TEID, oElem.BaseElement.Name, Sub, Voltage, Elem.Type.Split(':')[1], Elem["etx:Equipment.psseid"], FromBus, ToBus, Owner, Operator);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("Duplicated TEID: " + Elem.Type.Split(':')[1] + " " + Elem.Name + " (" + Sub + "; " + Elem.TEID + "; " + ex.Message + ")");
                    }
                    ExecuteCommand(sPos, sTrans, "INSERT INTO Contingency_Elements ([Contingency], [TEID]) VALUES (:0, :1)", ContingencyNumber, oElem.BaseElement.TEID);
                }
                sTrans.Commit();
            }
        }

        /// <summary>
        /// Export an XML file based on its definition
        /// </summary>
        /// <param name="xElem"></param>
        /// <param name="CtgGroups"></param>
        /// <param name="OutgoingDefinitions"></param>
        private void ExportXml(XmlElement xElem, Dictionary<string, MM_Contingency_Grouping> CtgGroups, Dictionary<string, MM_Contingency_Definition> OutgoingDefinitions)
        {
            using (StreamWriter sW = new StreamWriter(TargetDirectory + "\\" + xElem.Attributes["FileName"].Value.Replace("[CIMName]", Path.GetFileNameWithoutExtension(MM_Database_CIM.CIMFileName)), false, UTF8NoBOM))
            using (XmlTextWriter xW = new XmlTextWriter(sW))
            {
                xW.Formatting = Formatting.Indented;

                //Build our list of outgoing groups
                List<MM_Contingency_Grouping> OutGroups = new List<MM_Contingency_Grouping>();
                foreach (String GroupName in xElem.Attributes["Groups"].Value.Split(','))
                    OutGroups.Add(CtgGroups[GroupName]);

                //If we have radials to output, handle that
                List<int> OutRadials = new List<int>();
                if (xElem.HasAttribute("Radial"))
                    foreach (String rad in xElem.Attributes["Radial"].Value.Split(','))
                        OutRadials.Add(int.Parse(rad));

                //If we're exporting the full model, do so now.
                if (xElem.HasAttribute("FullModel") && XmlConvert.ToBoolean(xElem.Attributes["FullModel"].Value))
                {
                    sW.WriteLine("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
                    sW.WriteLine("<rdf:RDF xmlns:rdf=\"http://www.w3.org/1999/02/22-rdf-syntax-ns#\" xmlns:cim=\"http://iec.ch/TC57/2006/CIM-schema-cim10#\" xmlns:spc=\"http://www.siemens-ptd/SHIMM1.0#\" xmlns:etx=\"http://www.ercot.com/CIM11R0/2008/2.0/extension#\">");
                    foreach (CIM_Element Elem in MM_Database_CIM.Elements.Values)
                        if (Elem.Start > 0 && Elem.Type != "etx:ContingencyGroup" && Elem.Type != "etx:ContingencyElement" && Elem.Type != "etx:ContingencyGroupMember" && Elem.Type != "cim:Contingency" && Elem.Type != "etx:RemedialActionScheme")
                            sW.WriteLine(Elem.XML);
                }

                //Now, export all of our groups as requested.
                foreach (MM_Contingency_Grouping cGroup in OutGroups)
                    cGroup.WriteGrouping(xW);

                //Now, go through all contingency definitions, and export what we're looking for
                foreach (MM_Contingency_Definition cDef in OutgoingDefinitions.Values)
                {
                    bool Exported = false;
                    if (OutRadials.Count == 0 || OutRadials.Contains(cDef.Radial))
                        foreach (MM_Contingency_Grouping cGrp in cDef.Groups)
                            if (OutGroups.Contains(cGrp))
                            {
                                Exported = true;
                                cGrp.WriteGroupMember(xW, cDef);
                            }
                    if (Exported)
                        cDef.WriteDefinition(xW);
                }

                foreach (String str in MM_Database_CIM.Types.Keys)
                    if (str.Contains("Remedial"))
                        Console.WriteLine(str);

                //Now, write out our remedial action schemes
                foreach (CIM_Element RAS in MM_Database_CIM.ElementsOfType("etx:RemedialActionScheme"))
                    {
                        bool IsRAP = XmlConvert.ToBoolean(RAS.Attributes["etx:RemedialActionScheme.isRAP"]);
                        //Write out our document
                        xW.WriteStartElement("etx:RemedialActionScheme");
                        xW.WriteAttributeString("rdf:ID", RAS.rdfID.ToString());

                        foreach (KeyValuePair<String, String> kvp in RAS.Attributes)
                            if (kvp.Key == "Type" || kvp.Key == "etx:RemedialActionScheme.Contigency")
                            { }
                            else if (kvp.Value.StartsWith("#"))
                            {
                                xW.WriteStartElement(kvp.Key);
                                xW.WriteAttributeString("rdf:resource", kvp.Value);
                                xW.WriteEndElement();
                            }
                            else
                                xW.WriteElementString(kvp.Key, kvp.Value);

                        //Now, if we're a RAP, write out our link
                        MM_Contingency_Definition FoundCtg;
                        if (IsRAP)
                            if (!OutgoingDefinitions.TryGetValue(RAS.Name, out FoundCtg))
                                Console.WriteLine("ERROR! Can't find contingency to match RAS " + RAS.Name);
                            else
                            {
                                xW.WriteStartElement("etx:RemedialActionScheme.Contigency");
                                xW.WriteAttributeString("rdf:resource", "#"+ FoundCtg.rdfID.ToString());
                                xW.WriteEndElement();
                            }
                        xW.WriteEndElement();
                    }

                //Write our footer as needed
                xW.Flush();
                if (xElem.HasAttribute("FullModel") && XmlConvert.ToBoolean(xElem.Attributes["FullModel"].Value))
                    sW.WriteLine("</rdf:RDF>");
            }
        }

        /// <summary>
        /// Initiate the outgoing OS export
        /// </summary>
        [STAThread]
        public override void ProcessExporter()
        {
            //Check for Contingencies.SqLite File.
            CTGFileMissing = !File.Exists(TargetDirectory + "Contingencies.SQLite");

            ProcessThreadCollection processThreads = Process.GetCurrentProcess().Threads;


            //If it does not exist then show message box to copy file from previous location
            if (CTGFileMissing)
            {
                //Tell user the file is missing
                MessageBox.Show("No Contingencies.SQLite found. Please select the location of the previous Contingencies.SQLite file.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                
                //Show user directory to pick old file
                using (OpenFileDialog oFd = new OpenFileDialog())
                {
                    oFd.Title = "Select Previous Contingencies.SQLite file file";
                    oFd.Filter = "SQLite files (*.SQLite)|*.SQLite";
                    if (oFd.ShowDialog() == DialogResult.OK)
                    {
                        //Once the file has been selected copy the file to the current location
                        File.Copy(oFd.FileName, TargetDirectory + "Contingencies.SQLite", true);

                        //file now exist
                        CTGFileMissing = false;
                        
                    }
                    else
                    {
                        //Tell user no file was selected and the process must now end
                        MessageBox.Show("No Contingencies.SQLite file selected. The Application will now stop the Contingency Exporter.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        
                        //file is still missing
                        CTGFileMissing = true;
                        this.tFailedReason = "Missing Contingencies.SQLite File";

                        //End the contingency exporter process
                        this.threadFailed = true;
                        this.threadDictionary["Contingency Exporter"].Abort();
                    }
                }

            }

            SQLiteConnection sPos = new SQLiteConnection("Data Source=" + TargetDirectory + "Contingencies.SQLite");


            ////If exporting one-lines, prepare for their export
            //bool CreateOneLines = false;
            //frmOneLineEditor olEditor = null;
            //if (CreateOneLines)
            //{
            //    olEditor = new frmOneLineEditor(oDb, cDb);
            //    Application.DoEvents();
            //    olEditor.Show();
            //}

            ElementsToParse.Clear();
            this.Total = 0;

            //Now, open up our files
            Dictionary<CIM_Element, int> BreakerTally = new Dictionary<CIM_Element, int>();
            Dictionary<String, MM_Contingency_Definition> OutgoingDefinitions = new Dictionary<string, MM_Contingency_Definition>(StringComparer.CurrentCultureIgnoreCase);
            List<String> LogLines = new List<string>();

            //Build our list of seed elements from which contingency definitions will be created
            LocateSeedElements(CtgGroups, OutgoingDefinitions);

            //Now, go through all of our seed elements, and build our list of contingency definitions
            CIM_Element SeedElement;
            Dictionary<CIM_Element, CIM_TraceList> TraceList = new Dictionary<CIM_Element, CIM_TraceList>();
            
			//JLH
            //bool AddTables = !File.Exists(TargetDirectory + "Contingencies.SQLite");
            //SQLiteConnection sPos = new SQLiteConnection("Data Source=" + TargetDirectory + "Contingencies.SQLite");
            //DataTable SubToCounty = RetrieveSubstationToCountyLinkages();

            using (StreamWriter sLog = new StreamWriter(TargetDirectory + "ContingencyProcessing.log"))
            {
                sPos.Open();
                //if (CTGFileMissing)
                //    CreateContingencyFile(sPos);

                //Write out our companies
                ExecuteCommand(sPos, null, "DELETE FROM Companies");
                foreach (CIM_Element Company in MM_Database_CIM.ElementsOfType("cim:Company"))
                    ExecuteCommand(sPos, null, "INSERT INTO Companies ([TEID], [Name], [PrimaryPhone], [DUNS]) VALUES (:0, :1, :2, :3)", Convert.ToUInt64(Company.TEID), MM_Repository.TitleCase(Company.Name), Company["etx:Company.PrimaryPhone"], Company["etx:Company.DUNS"]);

                //Write the headers for our outgoing files
                ExecuteCommand(sPos, null, "DELETE FROM Contingencies");
                ExecuteCommand(sPos, null, "DELETE FROM Element_Positions");
                ExecuteCommand(sPos, null, "DELETE FROM Elements");
                ExecuteCommand(sPos, null, "DELETE FROM Contingency_Elements");


                int ContingencyNumber = 0;
                while (ElementsToParse.Count > 0)
                    using (Dictionary<object, bool>.Enumerator Enumerator = ElementsToParse.GetEnumerator())
                    {
                        Enumerator.MoveNext();
                        TraceList.Clear();
                        MM_rdfID OutID;
                        String Name, Description;
                        List<CIM_Element> FlaggedSeeds = new List<CIM_Element>();

                        if (Enumerator.Current.Key is CIM_Element)
                        {
                            SeedElement = Enumerator.Current.Key as CIM_Element;
                            TraceList = CIM_Tracer.Tracer(SeedElement, false, true, CtgAttribute);
                            OutID = new MM_rdfID("_" + Guid.NewGuid().ToString("B").ToUpper());
                            if (SeedElement.Type == "cim:ACLineSegment" || SeedElement.Type == "cim:SeriesCompensator")
                                Description = MM_Repository.TitleCase(SeedElement.ACSegmentStations[0].Name) + " To " + MM_Repository.TitleCase(SeedElement.ACSegmentStations[1].Name) + " " + SeedElement.VoltageLevel.Name + " / " + SeedElement.Parse("cim:Line").Name;
                            else
                                Description = MM_Repository.TitleCase(SeedElement.Substation.Name) + " " + SeedElement.Type.Split(':')[1] + " " + MM_Repository.TitleCase(SeedElement.Name);

                            Name = "N_" + OutgoingDefinitions.Count.ToString();

                            
                        }
                        else
                        {
                            OutID = Enumerator.Current.Key as MM_rdfID;
                            SeedElement = MM_Database_CIM.Elements[OutID];
                            foreach (CIM_Element Elem in MM_Database_CIM.Elements[Enumerator.Current.Key as MM_rdfID].Elements)
                                if (SeedElements.Contains(Elem.Type))
                                {
                                    FlaggedSeeds.Add(Elem);
                                    foreach (KeyValuePair<CIM_Element, CIM_TraceList> kvp in CIM_Tracer.Tracer(Elem, false, true, CtgAttribute))
                                        if (!TraceList.ContainsKey(kvp.Key))
                                            TraceList.Add(kvp.Key, kvp.Value);
                                }
                            Name = MM_Database_CIM.Elements[OutID].Name;
                            Description = MM_Repository.TitleCase(MM_Database_CIM.Elements[OutID]["cim:IdentifiedObject.description"]);
                            ElementsToParse.Remove(Enumerator.Current.Key);
                        }

                        //Now, export our contingency definition
                        MM_Contingency_Definition NewDef = new MM_Contingency_Definition(OutID, Name, Description, !Enumerator.Current.Value);
                        if (!Enumerator.Current.Value)
                        {
                            CIM_Element FoundElem = MM_Database_CIM.Elements[NewDef.rdfID];
                            NewDef.Seeds.Add(FoundElem);
                            if (FoundElem.Type == "cim:Breaker")
                                if (BreakerTally.ContainsKey(FoundElem))
                                    BreakerTally[FoundElem]++;
                                else
                                    BreakerTally.Add(FoundElem, 1);
                        }

                        //Go through our trace elements, and write it out
                        foreach (CIM_Element Elem in TraceList.Keys)
                        {
                            if (Enumerator.Current.Value)
                                if (ElementsToParse.Remove(Elem))
                                    NewDef.Seeds.Add(Elem);
                                else if (SeedElements.Contains(Elem.Type) && (XmlConvert.ToBoolean(Elem[CtgAttribute])))
                                    Console.WriteLine("Why was " + Elem.ToString() + " of type " + Elem.Type + " not in our seeds list?");

                            //If this type is in the exclusion critera with no parameters, do nothing
                            String ParamCheck = null;
                            if (IgnoreOnTrace.TryGetValue(Elem.Type, out ParamCheck) && ParamCheck == null)
                            { }

                            //If we have a parameter, check the exclusion criteria. If set to exclude, do nothing, otherwise export implicitly
                            else if (ParamCheck != null && Elem[ParamCheck.Split('=')[0]].Equals(ParamCheck.Split('=')[1], StringComparison.CurrentCultureIgnoreCase))
                            { }
                            else if (ParamCheck != null && !Elem[ParamCheck.Split('=')[0]].Equals(ParamCheck.Split('=')[1], StringComparison.CurrentCultureIgnoreCase))
                                NewDef.ExportableElements.Add(Elem, Guid.NewGuid());

                            //Otherwise, if we have a transformer winding look at power transformers, or look at the exportable element flag.                                                        
                            else if (Elem.Start == -1)
                                Console.WriteLine("**** ERROR! Element " + Elem.rdfID.ToString() + " has no XML! ****");

                            else if (IncludeSeedsInDoubles && FlaggedSeeds.Contains(Elem))
                                NewDef.ExportableElements.Add(Elem, Guid.NewGuid());

                            else if ((Elem.Type != "cim:TransformerWinding" && (String.IsNullOrEmpty(CtgAttribute) || String.IsNullOrEmpty(Elem[CtgAttribute]) || XmlConvert.ToBoolean(Elem[CtgAttribute]))) ||
                                (Elem.Type == "cim:TransformerWinding" && (String.IsNullOrEmpty(CtgAttribute) || String.IsNullOrEmpty(Elem.Parse("cim:PowerTransformer")[CtgAttribute]) || XmlConvert.ToBoolean(Elem.Parse("cim:PowerTransformer")[CtgAttribute]))))
                                NewDef.ExportableElements.Add(Elem, Guid.NewGuid());
                            
                        }
                        NewDef.Seeds.TrimExcess();
                        NewDef.FullElements = new List<CIM_Element>(TraceList.Keys);
                        NewDef.FullElements.TrimExcess();                  

                        //Assign our element's group membership, update its name, and export
                        foreach (MM_Contingency_Grouping cGrp in CtgGroups.Values)
                            if (cGrp.ContingencyInGroup(NewDef, TraceList.Keys))
                                NewDef.Groups.Add(cGrp);
                        String LastName = NewDef.Name;


                        bool FoundLine, CheckAutos;
                        List<CIM_Element> Substations, Units, XFs, Lines, ShuntCompensators;
                        List<MM_KVLevel> XFLevels, LineLevels;                                                  
                        NewDef.GenerateName(OutgoingDefinitions, sLog, sPos, LogLines, out FoundLine, out CheckAutos, out Substations, out Lines, out LineLevels, out XFLevels, out Units, out XFs, out ShuntCompensators);
                        NewDef.GenerateDescription(FoundLine, CheckAutos,  Substations, Lines, LineLevels,  XFLevels,  Units, XFs, ShuntCompensators);

                        //Check to see whether this contingency is to be monitored
                        /*if (NewDef.Name.Equals("Sgilnu28", StringComparison.CurrentCultureIgnoreCase))
                        {

                            StringBuilder sB2 = new StringBuilder();
                            int a = 0;
                            foreach (MM_Contingency_Definition cDef in OutgoingDefinitions.Values)
                            {
                                sB2.Append((++a).ToString() + "\t" + cDef.Name + "\t" + cDef.Description + "\t");
                                foreach (MM_Contingency_Grouping cGrp in cDef.Groups)
                                    sB2.Append(cGrp.Name + " ");
                                sB2.AppendLine();
                            }
                            Clipboard.SetText(sB2.ToString());
                        }
                        */
                        //If we have a N_ name that exists, update it. 
                        int CurNum = OutgoingDefinitions.Count;
                        if (OutgoingDefinitions.ContainsKey(NewDef.Name))
                        {
                            NewDef.Name = "N_" + (CurNum).ToString();
                            while (NewDef.Name.StartsWith("N_") && OutgoingDefinitions.ContainsKey("N_" + CurNum.ToString()))
                                NewDef.Name = "N_" + (++CurNum).ToString();
                        }
                        NewDef.Name = NewDef.Name.ToUpper();

                        //Further, check if our definition belongs to an EMS or MMS export with N_
                        String Groups = "";
                        if (NewDef.Name.StartsWith("N_") && NewDef.Groups.Count > 1)
                        {
                            foreach (MM_Contingency_Grouping cGrp in NewDef.Groups)
                                Groups += cGrp.Name;
                            if (Groups != "ALLPUNS")
                                Console.WriteLine("{0} appears to have not been named correctly.", NewDef.Name);
                        }
                            

                        
                        //Make sure we have no lower case stuff
                        foreach (Char c in NewDef.Name)
                            if (Char.IsLower(c))
                                Console.WriteLine("Whoopsie!");

                        OutgoingDefinitions.Add(NewDef.Name, NewDef);

                        //Write out our contingency output
                        //using (StreamWriter sTest = new StreamWriter(File.Open(TargetDirectory + Path.GetFileNameWithoutExtension(MM_Database_CIM.CIMFileName) + "-RawContingencyOutput.csv", FileMode.Append)))
                        //{
                        //    sTest.Write(Name);
                        //    foreach (CIM_Element Elem in TraceList.Keys)
                        //        sTest.Write("," + Elem.rdfID.ToString());
                        //    sTest.WriteLine();
                        //}

                        //Add our seed elements into our naming database
                        foreach (CIM_Element Seed in NewDef.Seeds)
                            if (!NewDef.FromCIM)
                                ExecuteCommand(sPos, null, "REPLACE INTO Contingency_Seeds ([Name],[TEID], [rdfID]) VALUES (:0, :1, :2)", NewDef.Name, MM_Database_CIM.ReverseTEIDs[Seed], NewDef.rdfID.ToString());


                        //Write out our contingency definition into our SQLite database
                        StringBuilder DefGroups = new StringBuilder();
                        foreach (MM_Contingency_Grouping cGroup in NewDef.Groups)
                            DefGroups.Append((DefGroups.Length == 0 ? "" : ",") + cGroup.Name);
                        ExecuteCommand(sPos, null, "INSERT INTO Contingencies ([INDEX],[Name], [Description], [Radial], [Groups]) VALUES (:0, :1, :2, :3, :4)", ++ContingencyNumber, NewDef.Name, NewDef.Description, -1, DefGroups.ToString());

                        //if (CreateOneLines)
                        //    ExportFullElements(NewDef, SeedElement, TraceList, CreateOneLines, sPos, olEditor, ContingencyNumber);

                        this.Current = this.Total - ElementsToParse.Count;
                    }
            }


            //Now, go through and define our radial components
            DefineRadials(BreakerTally, OutgoingDefinitions);

           
            //Export the XML for each contingency group defined in the configuration file
            foreach (MM_Contingency_Grouping Grouping in CtgGroups.Values)
            {
                Grouping.Export(TargetDirectory, OutgoingDefinitions);
                Application.DoEvents();
            }


            //Create our all all contingency file (-AllContingencies.xml)
            int CurExport = 0;
            using (XmlTextWriter xW = new XmlTextWriter(TargetDirectory + "\\AllContingencies.xml", UTF8NoBOM))
            {
                xW.Formatting = Formatting.Indented;
                foreach (MM_Contingency_Grouping Group in CtgGroups.Values)
                    Group.WriteGrouping(xW);
                foreach (MM_Contingency_Definition cDef in OutgoingDefinitions.Values)
                    if (MaxExport == -1 || CurExport < MaxExport)
                    {
                        foreach (MM_Contingency_Grouping Group in cDef.Groups)
                            Group.WriteGroupMember(xW, cDef);
                        cDef.WriteDefinition(xW);
                        CurExport++;
                    }
            }

            //Write out our contingencies in an XML format that can be read by the new version (-BreakerToBreakers.xml)
            using (XmlTextWriter xW = new XmlTextWriter(Path.Combine(TargetDirectory, Path.GetFileNameWithoutExtension(MM_Database_CIM.CIMFileName) + "-BreakerToBreakers.xml"), Encoding.UTF8))
            {
                xW.Formatting = Formatting.Indented;
                xW.WriteStartDocument();
                xW.WriteStartElement("BreakerToBreakers");
                foreach (MM_Contingency_Definition cDef in OutgoingDefinitions.Values)
                {
                    xW.WriteStartElement("BreakerToBreaker");
                    xW.WriteAttributeString("Name", cDef.Name);
                    xW.WriteAttributeString("Description", cDef.Description);
                    xW.WriteAttributeString("Radial", cDef.Radial.ToString());
                    xW.WriteAttributeString("rdfID", cDef.rdfID.ToString());



                    List<string> cDefGroups = new List<string>();
                    foreach (MM_Contingency_Grouping Group in cDef.Groups)
                        cDefGroups.Add(Group.Name);
                    xW.WriteAttributeString("Groups", String.Join(",", cDefGroups.ToArray()));

                    StringBuilder OutTEIDs = new StringBuilder();
                    StringBuilder OutRdfIDs = new StringBuilder();
                    StringBuilder OutRdfIDLinks = new StringBuilder();
                    UInt64 FoundTEID;
                    foreach (KeyValuePair<CIM_Element, Guid> kvp in cDef.ExportableElements)
                        if (OutRdfIDLinks.Length == 0)
                            OutRdfIDLinks.Append(kvp.Key.rdfID.ToString() + ",_" + kvp.Value.ToString("B") + ",");
                        else
                            OutRdfIDLinks.Append("," + kvp.Key.rdfID.ToString() + ",_" + kvp.Value.ToString("B") + ",");
                    


                    foreach (CIM_Element Elem in cDef.FullElements)
                    {
                        if (MM_Database_CIM.ReverseTEIDs.TryGetValue(Elem, out FoundTEID))
                            if (OutTEIDs.Length == 0)
                                OutTEIDs.Append(FoundTEID.ToString());
                            else
                                OutTEIDs.Append("," + FoundTEID.ToString());
                        if (OutRdfIDs.Length == 0)
                            OutRdfIDs.Append(Elem.rdfID.ToString());
                        else
                            OutRdfIDs.Append("," + Elem.rdfID.ToString());
                    }
                    xW.WriteAttributeString("TEIDs", OutTEIDs.ToString());
                    xW.WriteAttributeString("rdfIDs", OutRdfIDs.ToString());
                    xW.WriteAttributeString("linkRdfIDs", OutRdfIDLinks.ToString());

                    List<CIM_Element> OutSubs = new List<CIM_Element>();
                    foreach (CIM_Element Elem in cDef.FullElements)
                        if (Elem.Substation != null && !OutSubs.Contains(Elem.Substation))
                            OutSubs.Add(Elem.Substation);
                        else if (Elem.Type == "cim:ACLineSegment" || Elem.Type == "cim:SeriesCompensator")
                            foreach (CIM_Element Sub in Elem.ACSegmentStations)
                                if (!OutSubs.Contains(Sub))
                                    OutSubs.Add(Sub);

                    StringBuilder OutSubsStr = new StringBuilder();
                    StringBuilder OutSubsRdfIDs = new StringBuilder();
                    foreach (CIM_Element Sub in OutSubs)
                    {
                        if (MM_Database_CIM.ReverseTEIDs.TryGetValue(Sub, out FoundTEID))
                            OutSubsStr.Append((OutSubsStr.Length == 0 ? "" : ",") + FoundTEID.ToString());
                        OutSubsRdfIDs.Append((OutSubsRdfIDs.Length == 0 ? "" : ",") + Sub.rdfID.ToString());
                    }
                    xW.WriteAttributeString("SubstationTEIDs", OutSubsStr.ToString());
                    xW.WriteAttributeString("SubstationrdfIDs", OutSubsRdfIDs.ToString());
                    xW.WriteEndElement();
                }

                //Write out all our reserved names
                using (SQLiteCommand sCmd = new SQLiteCommand("SELECT [Name] from Reserved_Contingency_Names", sPos))
                using (SQLiteDataReader sRd = sCmd.ExecuteReader())
                    while (sRd.Read())
                    {
                        xW.WriteStartElement("ReservedTraceName");
                        xW.WriteAttributeString("Value", sRd["Name"].ToString());
                        xW.WriteEndElement();
                    }

                xW.WriteEndElement();
                xW.WriteEndDocument();
            }

            //Close our database
            sPos.Close();
            sPos.Dispose();

            //Write our export packages based off of the configuration file (i.e. -Incremental.xml & -PostProcessed.xml)      
            XmlElement CtgDef = MM_Configuration.xConfig.SelectSingleNode("/MacomberMap_Loader/ContingencyDefinitions") as XmlElement;
            foreach (XmlNode xNode in CtgDef.ChildNodes)
                if (xNode is XmlElement && xNode.Name == "Export")
                    ExportXml(xNode as XmlElement, CtgGroups, OutgoingDefinitions);

            //JLH
            //Export all definitions to an xlsx spreadsheet
            //WriteExcelSpreadsheet(OutgoingDefinitions, CtgGroups, SubToCounty, LogLines);
            WriteExcelSpreadsheet(OutgoingDefinitions, CtgGroups, LogLines);


            //Now, create our email           
            GenerateOutputEmail(OutgoingDefinitions);
        }
        #endregion

        /// <summary>
        /// Insert a row into a table
        /// </summary>
        /// <param name="dConn"></param>
        /// <param name="TableName"></param>
        /// <param name="OutValues"></param>
        private void InsertTableRow(DbConnection dConn, String TableName, Dictionary<String, object> OutValues)
        {
            StringBuilder sBd = new StringBuilder();
            StringBuilder sBd2 = new StringBuilder();
            int CurNum = 2;
            foreach (string StrToExport in OutValues.Keys)
                if (sBd.Length == 0)
                {
                    sBd.Append("INSERT INTO [" + TableName + "] ([" + StrToExport + "]");
                    sBd2.Append(") VALUES (:1");
                }
                else
                {
                    sBd.Append(",[" + StrToExport + "]");
                    sBd2.Append(",:" + CurNum++);
                }

            //Build our command, and execute
            using (DbCommand dCmd = dConn.CreateCommand())
            {
                dCmd.CommandText = sBd.ToString() + sBd2.ToString() + ")";
                dCmd.Prepare();
                CurNum = 1;
                foreach (Object obj in OutValues.Values)
                    AddParameter(dCmd, ":" + CurNum++, obj);
                dCmd.ExecuteNonQuery();
            }
        }



        /// <summary>
        /// Write out our Excel spreadsheet
        /// </summary>
        /// <param name="OutgoingDefinitions"></param>
        /// <param name="CtgGroups"></param>
        /// <param name="SubToCounty"></param>
        /// <param name="LogLines"></param>
        //private void WriteExcelSpreadsheet(Dictionary<string, MM_Contingency_Definition> OutgoingDefinitions, Dictionary<string, MM_Contingency_Grouping> CtgGroups, DataTable SubToCounty, List<String> LogLines)
        private void WriteExcelSpreadsheet(Dictionary<string, MM_Contingency_Definition> OutgoingDefinitions, Dictionary<string, MM_Contingency_Grouping> CtgGroups, List<String> LogLines)
        {
            Dictionary<String, Object> OutValues = new Dictionary<string, object>();
            using (OleDbConnection oleExcel = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path.Combine(TargetDirectory, Path.GetFileNameWithoutExtension(MM_Database_CIM.CIMFileName)) + "-Report.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES\";"))
//            using (OleDbConnection oleExcel = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path.Combine(TargetDirectory, Path.GetFileNameWithoutExtension(MM_Database_CIM.CIMFileName)) + "-Report.xls;Extended Properties=Excel 8.0;"))
            {
                oleExcel.Open();
                StringBuilder xCols = new StringBuilder("CREATE TABLE [Full List] ([Contingency ID] String, [Contingency Description] String, [Radial] Integer, [Substations] String, [Substation Long Names] String, [Counties] String, [Element type] String, [Element name] String, [Element TEID] string, [PSSE ID] String, [From bus] String, [To bus] String, [Owner] String, [Operator] String");
                foreach (MM_Contingency_Grouping cGrp in CtgGroups.Values)
                    xCols.Append(", [" + cGrp.Name + "] String");
                xCols.Append(")");

                using (OleDbCommand oCmd = new OleDbCommand(xCols.ToString(), oleExcel))
                    oCmd.ExecuteNonQuery();
                using (OleDbCommand oCmd = new OleDbCommand("CREATE TABLE [DeltaLog] ([Notes] String)", oleExcel))
                    oCmd.ExecuteNonQuery();

                //Now, export our log lines
                foreach (String str in LogLines)
                    using (OleDbCommand oCmd = new OleDbCommand("INSERT INTO [DeltaLog] ([Notes]) VALUES (:Note)", oleExcel))
                    {
                        oCmd.Prepare();
                        oCmd.Parameters.Add("Note", str);
                        oCmd.ExecuteNonQuery();
                    }


                //Now, export every contingency list
                SortedDictionary<String, MM_Contingency_Definition> SortedDefinitions = new SortedDictionary<string, MM_Contingency_Definition>(OutgoingDefinitions);
                foreach (MM_Contingency_Definition cDef in SortedDefinitions.Values)
                {
                    //Write our header list
                    OutValues.Clear();
                    OutValues.Add("Contingency ID", cDef.Name);
                    OutValues.Add("Contingency Description", cDef.Description);
                    OutValues.Add("Radial", cDef.Radial);

                    //Build our list of operators
                    List<String> Operators = new List<string>();
                    foreach (CIM_Element Elem in cDef.ExportableElements.Keys)
                        if (Elem.Operator != null && !Operators.Contains(Elem.Operator.Attributes["cim:IdentifiedObject.aliasName"]))
                            Operators.Add(Elem.Operator.Attributes["cim:IdentifiedObject.aliasName"]);
                    Operators.Sort();
                    OutValues.Add("Operator", String.Join(",",Operators.ToArray()));




                    foreach (MM_Contingency_Grouping cGrp in cDef.Groups)
                        OutValues.Add(cGrp.Name, "Yes");
                    InsertTableRow(oleExcel, "Full List", OutValues);


                    //Write our collection of exportable elements                     

                    foreach (CIM_Element Elem in cDef.ExportableElements.Keys)
                    {
                        OutValues.Clear();
                        if (Elem.Type == "cim:ACLineSegment" || Elem.Type == "cim:SeriesCompensator")
                        {
                            OutValues.Add("Substations", Elem.ACSegmentStations[0].Name + " to " + Elem.ACSegmentStations[1].Name);
                            String SubName;
                            String County;
                            DataRow FoundRow;

                            //JLH
                            SubName = Elem.ACSegmentStations[0].Name + " to ";
                            County = "Unknown";

                            //if ((FoundRow = SubToCounty.Rows.Find(Elem.ACSegmentStations[0].Name)) != null)
                            //{
                            //    SubName = FoundRow["Long Name"] + " to ";
                            //    if (FoundRow["County"] is DBNull)
                            //        County = "Unknown";
                            //    else
                            //        County = (FoundRow["County"] as MM_Boundary).Name;
                            //}
                            //else
                            //{
                            //    SubName = Elem.ACSegmentStations[0].Name + " to ";
                            //    County = "Unknown";
                            //}

                            SubName += Elem.ACSegmentStations[1].Name;
                            if (County != "Unknown")
                                County = County + " to Unknown";


                            //if ((FoundRow = SubToCounty.Rows.Find(Elem.ACSegmentStations[1].Name)) != null)
                            //{
                            //    SubName += FoundRow["Long Name"];
                            //    String TempCounty;
                            //    if (FoundRow["County"] is DBNull)
                            //        TempCounty = "Unknown";
                            //    else
                            //        TempCounty = (FoundRow["County"] as MM_Boundary).Name;
                            //    if (County != TempCounty)
                            //        County = County + " to " + TempCounty;
                            //}
                            //else
                            //{
                            //    SubName += Elem.ACSegmentStations[1].Name;
                            //    if (County != "Unknown")
                            //        County = County + " to Unknown";
                            //}

                            OutValues.Add("Substation Long Names", SubName);
                            OutValues.Add("Counties", County);
                            OutValues.Add("Element name", (Elem.Name.StartsWith("_") ? Elem.Parse("cim:Line").Name + Elem.Name : Elem.Parse("cim:Line").Name));
                        }
                        else
                        {
                            OutValues.Add("Substations", Elem.Substation.Name);
                            DataRow FoundRow;

                            OutValues.Add("Substation Long Names", Elem.Name);
                            OutValues.Add("Counties", "Unknown");

                            //if ((FoundRow = SubToCounty.Rows.Find(Elem.Substation.Name)) != null)
                            //{
                            //    OutValues.Add("Substation Long Names", FoundRow["Long Name"].ToString());
                            //    if (FoundRow["County"] is DBNull)
                            //        OutValues.Add("Counties", "Unknown");
                            //    else
                            //        OutValues.Add("Counties", (FoundRow["County"] as MM_Boundary).Name);
                            //}
                            //else
                            //{
                            //    OutValues.Add("Substation Long Names", Elem.Name);
                            //    OutValues.Add("Counties", "Unknown");
                            //}
                            OutValues.Add("Element name", Elem.Name);
                        }
                        OutValues.Add("Element type", Elem.Type.Split(':')[1]);
                        OutValues.Add("Element TEID", Elem.TEID);
                        OutValues.Add("PSSE ID", Elem["etx:Equipment.psseid"]);
                        
                        CIM_Element Owner = Elem.Owner;
                        if (Owner == null)
                            Owner = Elem.Parse("cim:Line>etx:Ownership>cim:Company");
                        if (Owner == null)
                            OutValues.Add("Owner", "");
                        else
                            OutValues.Add("Owner", Owner.Attributes["cim:IdentifiedObject.aliasName"]);


                        CIM_Element Operator = Elem.Operator;
                        if (Operator == null)
                            OutValues.Add("Operator", "");
                        else
                            OutValues.Add("Operator", Elem.Operator.Attributes["cim:IdentifiedObject.aliasName"]);


                        CIM_Element[] Nodes = Elem.Nodes;
                        if (Nodes.Length == 0)
                        {
                            OutValues.Add("From bus", "");
                            OutValues.Add("To bus", "");
                        }
                        else
                        {
                            OutValues.Add("From bus", Nodes[0]["etx:ConnectivityNode.PSSEBusNumber"]);

                            if (Nodes.Length > 1 && Nodes[0] != Nodes[1])
                                OutValues.Add("To bus", Nodes[1]["etx:ConnectivityNode.PSSEBusNumber"]);
                        }
                        InsertTableRow(oleExcel, "Full List", OutValues);
                    }
                }
            }

            //Now, open up our file in Excel, and perform our editing
            Excel.Application xlApp = new Excel.Application();
            xlApp.Visible = true;
            Excel.Workbook Book = xlApp.Workbooks.Open(Path.Combine(TargetDirectory, Path.GetFileNameWithoutExtension(MM_Database_CIM.CIMFileName)) + "-Report.xlsx", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            MM_Excel_Worksheet FullList = new MM_Excel_Worksheet(Book.Sheets["Full_List"] as Excel.Worksheet);
            FullList.Name = "Lookup_Table";
            FullList.Cells.Font.Name = "Calibri";
            FullList.Cells.Font.Size = 11;

            //Create our Lookup table
            MM_Excel_Worksheet LookupTable = new MM_Excel_Worksheet(Book.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Excel.Worksheet);
            LookupTable.Name = "Standard_Contingency_List";
            LookupTable.Cells.Font.Name = "Calibri";
            LookupTable.Cells.Font.Size = 11;
            LookupTable[1, 1].Value = "Name";
            LookupTable[1, 2].Value = "Description";
            LookupTable[1, 2].Value = "Operators";
            int OutRow = 2;
            for (int a = 2; a <= FullList.BaseSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Missing.Value).Row; a++)
                if (!String.IsNullOrEmpty(FullList[a, 1].get_Value(Missing.Value) as string))
                {
                    if (String.IsNullOrEmpty(FullList[a, 2].get_Value(Missing.Value) as string))
                        FullList[a, 2].Value = "(unknown)";
                    LookupTable[OutRow, 1].Value = FullList[a, 1].get_Value(Missing.Value);
                    LookupTable[OutRow, 2].Value = FullList[a, 2].get_Value(Missing.Value);
                    LookupTable[OutRow, 3].Value = FullList[a, 14].get_Value(Missing.Value);
                    LookupTable.Hyperlinks.Add(LookupTable.Cells[OutRow, 2], "", "='" + FullList.Name + "'!A" + a.ToString(), "", FullList[a, 2].get_Value(Missing.Value));
                    FullList.Hyperlinks.Add(FullList[a, 1], "", "='" + LookupTable.Name + "'!A" + OutRow.ToString(), "", FullList[a, 1].get_Value(Missing.Value));
                    OutRow++;
                }

            //Write out our introduction
            MM_Excel_Worksheet IntroductionTable = new MM_Excel_Worksheet(Book.Sheets.Add(Book.Sheets[1], Missing.Value, Missing.Value, Missing.Value) as Excel.Worksheet);
            IntroductionTable.Name = "Introduction";
            int ColWidth = 8;
            IntroductionTable.Cells.Font.Name = "Calibri";
            IntroductionTable.Cells.Font.Size = 11;

            //Add in our ERCOT image
            IntroductionTable.BaseSheet.Shapes.AddPicture(Path.Combine(System.Windows.Forms.Application.StartupPath, "ERCOT_Logo.bmp"), Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 0, 0, 290, 120);

            IntroductionTable[9, 1].Value = "Model:";
            IntroductionTable[9, 2].Value = Path.GetFileNameWithoutExtension(MM_Database_CIM.CIMFileName);
            IntroductionTable[9, 2, 9, ColWidth].Merge(Missing.Value);
            IntroductionTable[10, 1].Value = "Run on:";
            IntroductionTable[10, 2].Value = DateTime.Now.ToString();
            IntroductionTable[10, 2].HorizontalAlignment = Excel.Constants.xlLeft;
            IntroductionTable[10, 2, 10, ColWidth].Merge(Missing.Value);

            IntroductionTable[11, 1].Value = "Contingency Definition Guide";
            IntroductionTable[11, 1, 11, ColWidth].Merge(Missing.Value);
            IntroductionTable[11, 1].WrapText = true;
            IntroductionTable[11, 1].Font.Size = 22;
            IntroductionTable[11].RowHeight = 28;
            IntroductionTable[11, 1].HorizontalAlignment = Excel.Constants.xlCenter;


            IntroductionTable[13, 1].Value = "In nodal, contingency definitions are automatically generated against the network model, around transmission lines, units, and transformers, as well as for double-circuits. The definitions are built by starting at each seed element, and following the topology connections until finding breakers with relay action or open switches.";
            IntroductionTable[13, 1, 17, ColWidth].Merge(Missing.Value);
            IntroductionTable[13, 1].WrapText = true;

            IntroductionTable[19, 1].Value = "Double-Circuits:";
            IntroductionTable[19, 1].Font.Bold = true;
            IntroductionTable[19, 1, 19, ColWidth].Merge(Missing.Value);
            IntroductionTable[19, 1].WrapText = true;
            IntroductionTable[20, 1].Value = "Names starting with 'D'";
            IntroductionTable[20, 4].Value = "The network model contains the contingency name, and references to the latest seed elements. ";
            IntroductionTable[20, 4, 21, ColWidth].Merge(Missing.Value);
            IntroductionTable[20, 4].WrapText = true;



            IntroductionTable[23, 1].Value = "Single-circuit transmission lines:";
            IntroductionTable[23, 1].Font.Bold = true;
            IntroductionTable[24, 1].Value = "[Letter 'S']";
            IntroductionTable[25, 1].Value = "[First three letters of the substation with highest total normal line MVA ratings]";
            IntroductionTable[26, 1].Value = "[First three letters of the substation with lowest total normal line MVA ratings]";
            IntroductionTable[27, 1].Value = "[The last number in the KV (e.g., 5 for 345 KV)]";

            IntroductionTable[29, 1].Value = "Transformers:";
            IntroductionTable[29, 1].Font.Bold = true;
            IntroductionTable[30, 1].Value = "[Letter 'X']";
            IntroductionTable[31, 1].Value = "[First three letters of the substation]";
            IntroductionTable[32, 1].Value = "[The last number in the high-side KV (e.g., 5 for 345 KV)]";
            IntroductionTable[33, 1].Value = "[The last number in the low-side KV (e.g., 9 for 69 KV)]";


            IntroductionTable[35, 1].Value = "Units:";
            IntroductionTable[35, 1].Font.Bold = true;
            IntroductionTable[36, 1].Value = "[Letter 'U']";
            IntroductionTable[37, 1].Value = "[First three letters of the substation]";
            IntroductionTable[38, 1].Value = "[First three letters of the unit]";
            IntroductionTable[39, 1].Value = "[Number of units in the definition]";

            IntroductionTable[41, 1].Value = "Shunt Compensators:";
            IntroductionTable[41, 1].Font.Bold = true;
            IntroductionTable[42, 1].Value = "[Letter 'Q']";
            IntroductionTable[43, 1].Value = "[First three letters of the substation]";
            IntroductionTable[44, 1].Value = "[First three letters of the shunt compensator]";
            IntroductionTable[45, 1].Value = "[The last number in the KV (e.g., 5 for 345 KV)]";

            IntroductionTable[47, 1].Value = "Others:";
            IntroductionTable[47, 1].Font.Bold = true;
            IntroductionTable[48, 1].Value = "Breaker-to-breaker definitions utilized in a one-line program by ERCOT staff. These names start with N_ and a number, and are not exported to or used by EMS, MMS or OS systems.";
            IntroductionTable[48, 1, 48, ColWidth].Merge(Missing.Value);
            IntroductionTable[48, 1].WrapText = true;

            IntroductionTable[50, 1].Value = "Duplication handling:";
            IntroductionTable[50, 1].Font.Bold = true;
            IntroductionTable[51, 1].Value = "In the event that a contingency name is duplicated, the last letter of the second substation (single-circuit line), or substation (transformers or units) is replaced by a number.";
            IntroductionTable[51, 1, 51, ColWidth].Merge(Missing.Value);
            IntroductionTable[51, 1].WrapText = true;

            //Delete "Sheet2"
            xlApp.DisplayAlerts = false;
            foreach (Excel.Worksheet sheet in Book.Sheets)
            {
                if (sheet.Name == "Sheet2")
                {
                    Book.Worksheets["Sheet2"].delete();
                    break;
                }
            }
            xlApp.DisplayAlerts = true;

            Book.Save();
            xlApp.Quit();
        }

        public void FormatExcel()
        {
            //Now, open up our file in Excel, and perform our editing
            Excel.Application xlApp = new Excel.Application();
            xlApp.Visible = true;
            Excel.Workbook Book = xlApp.Workbooks.Open(Path.Combine(TargetDirectory, Path.GetFileNameWithoutExtension(MM_Database_CIM.CIMFileName)) + "-Report.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            //Excel.Workbook Book = xlApp.Workbooks.Open("C:\\Documents and Settings\\nmago\\Desktop\\CIM_Apr_ML1_1_04092013_Complete_StandardContingencyReport.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            MM_Excel_Worksheet FullList = new MM_Excel_Worksheet(Book.Sheets["Full_List"] as Excel.Worksheet);
            FullList.Name = "Lookup_Table";
            FullList.Cells.Font.Name = "Calibri";
            FullList.Cells.Font.Size = 11;

            //Create our Lookup table
            MM_Excel_Worksheet LookupTable = new MM_Excel_Worksheet(Book.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Excel.Worksheet);
            LookupTable.Name = "Standard_Contingency_List";
            LookupTable.Cells.Font.Name = "Calibri";
            LookupTable.Cells.Font.Size = 11;
            LookupTable[1, 1].Value = "Name";
            LookupTable[1, 2].Value = "Description";
            int OutRow = 2;
            for (int a = 2; a <= FullList.BaseSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Missing.Value).Row; a++)
                if (!String.IsNullOrEmpty(FullList[a, 1].get_Value(Missing.Value) as string))
                {
                    if (String.IsNullOrEmpty(FullList[a, 2].get_Value(Missing.Value) as string))
                        FullList[a, 2].Value = "(unknown)";
                    LookupTable[OutRow, 1].Value = FullList[a, 1].get_Value(Missing.Value);
                    LookupTable[OutRow, 2].Value = FullList[a, 2].get_Value(Missing.Value);
                    LookupTable.Hyperlinks.Add(LookupTable.Cells[OutRow, 2], "", "='" + FullList.Name + "'!A" + a.ToString(), "", FullList[a, 2].get_Value(Missing.Value));
                    FullList.Hyperlinks.Add(FullList[a, 1], "", "='" + LookupTable.Name + "'!A" + OutRow.ToString(), "", FullList[a, 1].get_Value(Missing.Value));
                    OutRow++;
                }

            //Write out our introduction
            MM_Excel_Worksheet IntroductionTable = new MM_Excel_Worksheet(Book.Sheets.Add(Book.Sheets[1], Missing.Value, Missing.Value, Missing.Value) as Excel.Worksheet);
            IntroductionTable.Name = "Introduction";
            int ColWidth = 8;
            IntroductionTable.Cells.Font.Name = "Calibri";
            IntroductionTable.Cells.Font.Size = 11;

            //Add in our ERCOT image

            IntroductionTable.BaseSheet.Shapes.AddPicture(Path.Combine(Application.StartupPath, "ERCOT_Logo.bmp"), Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 0, 0, 326, 132);

            IntroductionTable[9, 1].Value = "Model:";
            IntroductionTable[9, 2].Value = Path.GetFileNameWithoutExtension(MM_Database_CIM.CIMFileName);
            IntroductionTable[9, 2, 9, ColWidth].Merge(Missing.Value);
            IntroductionTable[10, 1].Value = "Run on:";
            IntroductionTable[10, 2].Value = DateTime.Now.ToString();
            IntroductionTable[10, 2].HorizontalAlignment = Excel.Constants.xlLeft;
            IntroductionTable[10, 2, 10, ColWidth].Merge(Missing.Value);

            IntroductionTable[11, 1].Value = "Contingency Definition Guide";
            IntroductionTable[11, 1, 11, ColWidth].Merge(Missing.Value);
            IntroductionTable[11, 1].WrapText = true;
            IntroductionTable[11, 1].Font.Size = 22;
            IntroductionTable[11].RowHeight = 28;
            IntroductionTable[11, 1].HorizontalAlignment = Excel.Constants.xlCenter;


            IntroductionTable[13, 1].Value = "In nodal, contingency definitions are automatically generated against the network model, around transmission lines, units, and transformers, as well as for double-circuits. The definitions are built by starting at each seed element, and following the topology connections until finding breakers with relay action or open switches.";
            IntroductionTable[13, 1, 17, ColWidth].Merge(Missing.Value);
            IntroductionTable[13, 1].WrapText = true;

            IntroductionTable[19, 1].Value = "Double-Circuits:";
            IntroductionTable[19, 1].Font.Bold = true;
            IntroductionTable[19, 1, 19, ColWidth].Merge(Missing.Value);
            IntroductionTable[19, 1].WrapText = true;
            IntroductionTable[20, 1].Value = "Names starting with 'D'";
            IntroductionTable[20, 4].Value = "The network model contains the contingency name, and references to the latest seed elements. ";
            IntroductionTable[20, 4, 21, ColWidth].Merge(Missing.Value);
            IntroductionTable[20, 4].WrapText = true;



            IntroductionTable[23, 1].Value = "Single-circuit transmission lines:";
            IntroductionTable[23, 1].Font.Bold = true;
            IntroductionTable[24, 1].Value = "[Letter 'S']";
            IntroductionTable[25, 1].Value = "[First three letters of the substation with highest total normal line MVA ratings]";
            IntroductionTable[26, 1].Value = "[First three letters of the substation with lowest total normal line MVA ratings]";
            IntroductionTable[27, 1].Value = "[The last number in the KV (e.g., 5 for 345 KV)]";

            IntroductionTable[29, 1].Value = "Transformers:";
            IntroductionTable[29, 1].Font.Bold = true;
            IntroductionTable[30, 1].Value = "[Letter 'X']";
            IntroductionTable[31, 1].Value = "[First three letters of the substation]";
            IntroductionTable[32, 1].Value = "[The last number in the high-side KV (e.g., 5 for 345 KV)]";
            IntroductionTable[33, 1].Value = "[The last number in the low-side KV (e.g., 9 for 69 KV)]";


            IntroductionTable[35, 1].Value = "Units:";
            IntroductionTable[35, 1].Font.Bold = true;
            IntroductionTable[36, 1].Value = "[Letter 'U']";
            IntroductionTable[37, 1].Value = "[First three letters of the substation]";
            IntroductionTable[38, 1].Value = "[First three letters of the unit]";
            IntroductionTable[39, 1].Value = "[Number of units in the definition]";

            IntroductionTable[41, 1].Value = "Others:";
            IntroductionTable[41, 1].Font.Bold = true;
            IntroductionTable[42, 1].Value = "Breaker-to-breaker definitions utilized in a one-line program by ERCOT staff. These names start with N_ and a number, and are not exported to or used by EMS, MMS or OS systems.";
            IntroductionTable[42, 1, 43, ColWidth].Merge(Missing.Value);
            IntroductionTable[42, 1].WrapText = true;

            IntroductionTable[47, 1].Value = "Duplication handling:";
            IntroductionTable[47, 1].Font.Bold = true;
            IntroductionTable[48, 1].Value = "In the event that a contingency name is duplicated, the last letter of the second substation (single-circuit line), or substation (transformers or units) is replaced by a number.";
            IntroductionTable[48, 1, 49, ColWidth].Merge(Missing.Value);
            IntroductionTable[48, 1].WrapText = true;
            Book.SaveCopyAs("C:\\Documents and Settings\\nmago\\Desktop\\CIM_Apr_ML1_1_04092013_Complete_StandardContingencyReport_2.xls");

            xlApp.Quit();
        }


        /// <summary>
        /// Define our list of radials
        /// </summary>
        /// <param name="BreakerTally">Our collection of breakers</param>
        /// <param name="OutgoingDefinitions">Our collection of outgoing defintions</param>
        /// <remarks>Modified 12/8/2011 to ignore M* contingencies in radial definitions</remarks>
        private void DefineRadials(Dictionary<CIM_Element, int> BreakerTally, Dictionary<String, MM_Contingency_Definition> OutgoingDefinitions)
        {
            Dictionary<CIM_Element, bool> RadialBreakers = new Dictionary<CIM_Element, bool>();
            bool FoundRadial = true;
            int CurIncrement = 0;
            while (FoundRadial)
            {
                FoundRadial = false;
                foreach (MM_Contingency_Definition cDef in OutgoingDefinitions.Values)
                    if (!cDef.ManualCtg && cDef.Radial == -1 && !cDef.FromCIM && cDef.CheckRadial(RadialBreakers, CtgAttribute, CurIncrement, BreakerTally))
                        FoundRadial = true;
                CurIncrement++;
            }
        }

        /// <summary>
        /// Generate the output email
        /// </summary>
        /// <param name="OutgoingDefinitions"></param>
        private void GenerateOutputEmail(Dictionary<string, MM_Contingency_Definition> OutgoingDefinitions)
        {

            Dictionary<String, int> Groups = new Dictionary<String, int>();
            foreach (MM_Contingency_Definition cDef in OutgoingDefinitions.Values)
            {
                StringBuilder Grouping = new StringBuilder();
                for (int a = 0; a < cDef.Groups.Count; a++)
                    if (Grouping.Length == 0)
                        Grouping.Append(cDef.Groups[a].Name);
                    else
                        Grouping.Append("," + cDef.Groups[a].Name);

                if (Groups.ContainsKey(Grouping.ToString()))
                    Groups[Grouping.ToString()]++;
                else
                    Groups.Add(Grouping.ToString(), 1);
                Application.DoEvents();
            }

            //Now, determine the maximum # of elements
            int MaxVal = 0;
            foreach (int val in Groups.Values)
                MaxVal = Math.Max(MaxVal, val);

            StringBuilder OutString = new StringBuilder();
            OutString.AppendLine(String.Format("This is the Macomber Map Loader's Contingency Generator Module, version {0}, running on {1}, by {2}\\{3} at {4}", Application.ProductVersion, Environment.MachineName, Environment.UserDomainName, Environment.UserName, DateTime.Now));
            OutString.AppendLine();
            OutString.AppendLine(String.Format("The contingency generator/tracer was ran against CIM file {0}, and the results have been saved into {1}.", MM_Database_CIM.CIMFileName, TargetDirectory));
            OutString.AppendLine();
            OutString.AppendLine("Groupings:");
            for (int a = MaxVal; a >= 0; a--)
                foreach (KeyValuePair<String, int> kvp in Groups)
                    if (kvp.Value == a)
                        OutString.AppendLine(kvp.Value.ToString() + ": " + kvp.Key);

            XmlElement BaseNode = MM_Configuration.xConfig.SelectSingleNode("/MacomberMap_Loader/ContingencyDefinitions") as XmlElement;
            MM_Email_Sending.SendEmail(MM_Configuration.xConfig["MacomberMap_Loader"]["SMTPServer"].Attributes["Value"].Value, BaseNode.Attributes["FromEmail"].Value, BaseNode.Attributes["FromEmail"].Value, BaseNode.Attributes["Subject"].Value, BaseNode.Attributes["MailRecipients"].Value.Split(','), BaseNode.Attributes["CarbonCopies"].Value.Split(','), BaseNode.Attributes["BlindCarbonCopies"].Value.Split(','), OutString.ToString(), Path.Combine(TargetDirectory, Path.GetFileNameWithoutExtension(MM_Database_CIM.CIMFileName)) + "-Report.xlsx");


        }
    }
}